﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;



namespace WindowsFormsApplication3.Transaction
{
    class CompanyPaymentTransaction
    {


        public void insertpaymentintodb(DataBean.CompanyPaymentDatabean companypaymentbean)
        {

                OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection(Program.ConnStr);
                oleDbConnection1.Open();
                String querystrng = "INSERT INTO companypaymentmastertable (cmpinvoice,jobcode,jobpk,customerpk,customercode,quotationpk,customerName,ischeque,isCash,amount ,chequenumber ,bankname,confirmpk,chequedate ,chequereleasedate,companypaymentdate) Values (@cmpinvoice,@jobcode , @jobpk , @customerpk ,@Customercode ,@quotationpk ,@customername ,@ischeque ,@iscash,@amount ,@chequenum, @bankname ,@confirmpk ,@chequedate ,@chequereleasedate, @companypaymentdate)";
                OleDbCommand oleDbCommand1 = new System.Data.OleDb.OleDbCommand(querystrng, oleDbConnection1);
                oleDbCommand1.Parameters.AddWithValue("@cmpinvoice ", companypaymentbean.Cmpinvoice);
                oleDbCommand1.Parameters.AddWithValue("@jobcode ", companypaymentbean.Jobcode);
                oleDbCommand1.Parameters.AddWithValue("@jobpk ", companypaymentbean.Jobpk);
                oleDbCommand1.Parameters.AddWithValue("@customerpk ", companypaymentbean.Customerpk);
                oleDbCommand1.Parameters.AddWithValue("@Customercode ", companypaymentbean.Customercode);

                oleDbCommand1.Parameters.AddWithValue("@quotationpk ", companypaymentbean.Quotationpk);
                oleDbCommand1.Parameters.AddWithValue("@customername ", companypaymentbean.Customername);
                oleDbCommand1.Parameters.AddWithValue("@ischeque ", companypaymentbean.Ischeque);
                oleDbCommand1.Parameters.AddWithValue("@iscash ", companypaymentbean.Iscash);
                oleDbCommand1.Parameters.AddWithValue("@amount ", companypaymentbean.Amount);
                oleDbCommand1.Parameters.AddWithValue("@chequenum ", companypaymentbean.Chequeno);
                oleDbCommand1.Parameters.AddWithValue("@bankname  ", companypaymentbean.BankName);
                oleDbCommand1.Parameters.AddWithValue("@confirmpk ", companypaymentbean.Confirmpk);
                oleDbCommand1.Parameters.AddWithValue("@chequedate ", companypaymentbean.Chequedate);
                oleDbCommand1.Parameters.AddWithValue("@chequereleasedate", companypaymentbean.Chequereleasedate);
                oleDbCommand1.Parameters.AddWithValue(" @companypaymentdate ", companypaymentbean.Paymentdate);
                oleDbCommand1.CommandType = CommandType.Text;
                oleDbCommand1.ExecuteNonQuery();

                oleDbConnection1.Close();
                
           
               
           


        }

        public String getamountbyagreedquotation(int qtnpk)
        {
            string amount = "";

            OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection(Program.ConnStr);
            oleDbConnection1.Open();

            OleDbCommand oleDbCommand1 = new System.Data.OleDb.OleDbCommand("Select confirmpk ,quotationcode , agrdamount from  confirmquotationmastertable  where confirmpk = " + qtnpk + "  ", oleDbConnection1);



            OleDbDataReader reader = oleDbCommand1.ExecuteReader();


            DataTable dt = new DataTable();

            dt.Columns.Add("confirmpk", typeof(int));
            dt.Columns.Add("quotationcode", typeof(string));
            dt.Columns.Add("amount", typeof(int));
            dt.Load(reader);

            if (dt.Rows.Count != 0)
            {

               

              amount=  dt.Rows[0][3].ToString();
              Console.WriteLine(amount.ToString());
              return amount;
 

               
            }
            else
            {
                amount = "";
                
              
            }
            

            oleDbConnection1.Close();
            return amount;
           
        }


        public DataSet showallCompanyPaymentbyjobcode(int jobpk ,int confirmquotationpk)
        {


            String query = " SELECT companypaymentmastertable.paymentpk, companypaymentmastertable.cmpinvoice, companypaymentmastertable.jobcode, companypaymentmastertable.customercode, confirmquotationmastertable.quotationcode, companypaymentmastertable.customerName, companypaymentmastertable.ischeque, companypaymentmastertable.isCash, companypaymentmastertable.amount, companypaymentmastertable.chequenumber, companypaymentmastertable.bankname, companypaymentmastertable.chequedate, companypaymentmastertable.chequereleasedate, companypaymentmastertable.companypaymentdate FROM confirmquotationmastertable INNER JOIN companypaymentmastertable ON confirmquotationmastertable.confirmpk=companypaymentmastertable.confirmpk FROM confirmquotationmastertable INNER JOIN companypaymentmastertable ON confirmquotationmastertable.confirmpk=companypaymentmastertable.confirmpk WHERE (((companypaymentmastertable.confirmpk)=@confirmpk) AND ((companypaymentmastertable.jobpk)=@jobpk))";

           OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, Program.ConnStr);
           dAdapter.SelectCommand.Parameters.AddWithValue("@confirmpk", confirmquotationpk);
           dAdapter.SelectCommand.Parameters.AddWithValue("@jobpk", jobpk);
                DataSet ds = new DataSet();
                dAdapter.Fill(ds, "tblpayview");
               
                if (ds.Tables.Count <= 0)
                {
                    ds = null;    
                }

                return ds;

            }





        public String  newinvoiceget()
        {
            String invoiceno = null;
            try
            {
               
                Int32 S = 0;
              //  lblinvoice.Text = "";
                OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection(Program.ConnStr);
                oleDbConnection1.Open();


                String query = "SELECT TOP 1 paymentpk FROM companypaymentmastertable ORDER BY paymentpk DESC";
                OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, Program.ConnStr);


                DataSet ds = new DataSet();

                dAdapter.Fill(ds, "tblpaymentpk");
                if (ds.Tables[0].Rows.Count == 0)
                {
                    S = 0;
                }
                else
                {
                    S = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                }

                S++;

                invoiceno = "CINV#0" + S.ToString();

             //   lblinvoice.Text = "CINV#0" + S.ToString();
              //  lblinvoice.Enabled = false;
                
            }
            catch (Exception )
            {

              //  MessageBox.Show("Failed to autogenerate INVOICE ");
              //  MessageBox.Show(exp.ToString());
              //  this.Close();
                invoiceno = "###";
              
            }



            return invoiceno;

           
        }







        }
    

    }

